from sqlalchemy import Column, String, Integer, create_engine, Table, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

__author__ = 'WhiteBlue'

Base = declarative_base()


class DbUtil(object):
    __entity_map = []
    metadata = MetaData()

    @classmethod
    def init(cls, url):
        try:
            cls.engine = create_engine(url, echo=True)
            cls.DBSession = sessionmaker(bind=DbUtil.engine)
        except Exception as e:
            print('Error:init error', e)

    @staticmethod
    def get_session():
        return DbUtil.DBSession()

    @staticmethod
    def create_table():
        DbUtil.metadata.create_all(DbUtil.engine)


# 用户
class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(40))
    email = Column(String(60))
    type = Column(Integer)  # 用户组

    table = Table('user', DbUtil.metadata,
                  Column('id', Integer, primary_key=True, autoincrement=True),
                  Column('name', String(40)),
                  Column('email', String(60)),
                  Column('type', Integer)
                  )


# 分类
class Sort(Base):
    __tablename__ = 'sort'

    id = Column(Integer, primary_key=True)
    name = Column(String(40))
    count = Column(Integer)  # 包含文章数量

    table = Table('sort', DbUtil.metadata,
                  Column('id', Integer, primary_key=True, autoincrement=True),
                  Column('name', String(40)),
                  Column('count', Integer)
                  )


# 文章
class Article(Base):
    __tablename__ = 'article'

    id = Column(Integer, primary_key=True)
    title = Column(String(40))
    content = Column(String)
    post_count = Column(Integer)  # 回复数量

    sort_id = Column(Integer, ForeignKey('sort.id'))  # 分类外键
    user_id = Column(Integer, ForeignKey('user.id'))  # 用户外键

    table = Table('article', DbUtil.metadata,
                  Column('id', Integer, primary_key=True, autoincrement=True),
                  Column('title', String(40)),
                  Column('content', String),
                  Column('post_count', Integer),
                  Column('sort_id', Integer, ForeignKey('sort.id')),
                  Column('user_id', Integer, ForeignKey('user.id'))
                  )


class Post(Base):
    __tablename__ = 'post'

    id = Column(Integer, primary_key=True)
    content = Column(String)
    poster_name = Column(String(40))
    poster_url = Column(String(100))

    article_id = Column(Integer, ForeignKey('article.id'))  # 文章外键

    table = Table('post', DbUtil.metadata,
                  Column('id', Integer, primary_key=True, autoincrement=True),
                  Column('content', String),
                  Column('poster_name', String(40)),
                  Column('poster_url', String(100)),
                  Column('article_id', Integer, ForeignKey('article.id'))
                  )


if __name__ == '__main__':
    DbUtil.init('sqlite:///./myBlog.db')

    DbUtil.metadata.create_all(DbUtil.engine)

    session = DbUtil.get_session()

    user = User(name='whiteblue', email='zero534443992@outlook.com', type=0)

    session.add(user)

    session.commit()

    session.close()

# session = DBSession()
#
# # 创建新User对象:
# new_user = User(id='5', name='Bob')
#
# # 添加到session:
# session.add(new_user)
# # 提交即保存到数据库:
# session.commit()
# # 关闭session:
# session.close()
